package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import object.BoardDataBean;

public class BoardDAO {

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	private static BoardDAO instance = new BoardDAO();

	public static BoardDAO getInstance() {
		return instance;
	}

	private BoardDAO() {
	}

	private Connection getConnection() throws Exception {
		Context ctx = new InitialContext();
		DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/OracleDB");
		return ds.getConnection();
	}

	public void insertArticle(BoardDataBean article) {
		int num = article.getNum();
		int ref = article.getRef();
		int re_step = article.getRe_step();
		int re_level = article.getRe_level();

		int number = 0;
		String sql = null;

		try {
			conn = getConnection();

			pstmt = conn.prepareStatement("select max(num) from board");
			rs = pstmt.executeQuery();

			if (rs.next()) {
				number = rs.getInt(1) + 1;
			} else {
				number = 1;
			}

			if (num != 0) {
				sql = "update board set re_step = re_step+1 where ref = ? and re_step > ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, ref);
				pstmt.setInt(2, re_step);
				pstmt.executeUpdate();

				re_step = re_step + 1;
				re_level = re_level + 1;
			} else {
				ref = number;
				re_step = 0;
				re_level = 0;
			}

			sql = "insert into board "
					+ "(num, writer, email, subject, passwd, reg_date, "
					+ " ref, re_step, re_level, content, ip) "
					+ "values(SEQ_BOARD_NO.nextval,?,?,?,?,?,?,?,?,?,?)";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, article.getWriter());
			pstmt.setString(2, article.getEmail());
			pstmt.setString(3, article.getSubject());
			pstmt.setString(4, article.getPass());
			pstmt.setDate(5, article.getReg_date());
			pstmt.setInt(6, ref);
			pstmt.setInt(7, re_step);
			pstmt.setInt(8, re_level);
			pstmt.setString(9, article.getContent());
			pstmt.setString(10, article.getIp());

			pstmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			freeConnection();
		}
	}

	private void freeConnection() {

		try {
			if (rs != null)	rs.close();
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getArticleCount() {
		int count = 0;
		
		try {
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select count(*) from board");
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				count = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			freeConnection();
		}
		return count;
	}
	
	public BoardDataBean getArticle(int num){
		BoardDataBean article = null;
		
		try {
			conn = getConnection();
			
			pstmt = conn.prepareStatement(
					"update board set readcount=readcount+1 where num = ?");
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement(
					"select * from board where num = ?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				article = new BoardDataBean();
				article.setNum(rs.getInt("num"));
				article.setWriter(rs.getString("writer"));
				article.setEmail(rs.getString("email"));
				article.setSubject(rs.getString("subject"));
				article.setPass(rs.getString("passwd"));
				article.setReg_date(rs.getDate("reg_date"));
				article.setRead_count(rs.getInt("readcount"));
				article.setRef(rs.getInt("ref"));
				article.setRe_step(rs.getInt("re_step"));
				article.setRe_level(rs.getInt("re_level"));
				article.setContent(rs.getString("content"));
				article.setIp(rs.getString("ip"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			freeConnection();
		}
		
		return article;
	}
	

	public List<BoardDataBean> getArticles(int startRow, int endRow) {
		
		List<BoardDataBean> articleList = null;
		
		String sql = "SELECT * FROM (SELECT ROWNUM AS rn, a.* FROM" +
				"(SELECT * FROM board ORDER BY ref desc, re_step asc)a) " +
				"WHERE rn BETWEEN ? and ?";
		
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, startRow - 1);
			pstmt.setInt(2, endRow);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				articleList = new ArrayList<BoardDataBean>();
				do{
					BoardDataBean article = new BoardDataBean();
					article.setNum(rs.getInt("num"));
					article.setWriter(rs.getString("writer"));
					article.setEmail(rs.getString("email"));
					article.setSubject(rs.getString("subject"));
					article.setPass(rs.getString("passwd"));
					article.setReg_date(rs.getDate("reg_date"));
					article.setRead_count(rs.getInt("readcount"));
					article.setRef(rs.getInt("ref"));
					article.setRe_step(rs.getInt("re_step"));
					article.setRe_level(rs.getInt("re_level"));
					article.setContent(rs.getString("content"));
					article.setIp(rs.getString("ip"));
					
					articleList.add(article);
				}while(rs.next());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			freeConnection();
		}
		
		return articleList;
	}
	
	
	public BoardDataBean updateGetArticle(int num){
		BoardDataBean article = null;
		
		try {
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select * from board where num = ?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				article = new BoardDataBean();
				article.setNum(rs.getInt("num"));
				article.setWriter(rs.getString("writer"));
				article.setEmail(rs.getString("email"));
				article.setSubject(rs.getString("subject"));
				article.setPass(rs.getString("passwd"));
				article.setReg_date(rs.getDate("reg_date"));
				article.setRead_count(rs.getInt("readcount"));
				article.setRef(rs.getInt("ref"));
				article.setRe_step(rs.getInt("re_step"));
				article.setRe_level(rs.getInt("re_level"));
				article.setContent(rs.getString("content"));
				article.setIp(rs.getString("ip"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			freeConnection();
		}
		return article;
	}
	
	public int updateArticle(BoardDataBean article){
		String dbPass = null;
		int check = -1;
		
		try {
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select passwd from board where num = ?");
			pstmt.setInt(1, article.getNum());
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				dbPass = rs.getString("passwd");
				if(dbPass.equals(article.getPass())){
					pstmt = conn.prepareStatement(
							"update board set writer = ?, email = ?, " +
							"subject = ?, passwd = ?, content = ? where num = ?");
					pstmt.setString(1, article.getWriter());
					pstmt.setString(2, article.getEmail());
					pstmt.setString(3, article.getSubject());
					pstmt.setString(4, article.getPass());
					pstmt.setString(5, article.getContent());
					pstmt.setInt(6, article.getNum());
					pstmt.executeUpdate();
					check =  1;
				}else{
					check =  0;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			freeConnection();
		}
		return check;
	}
	
	public int deleteArticle(int num, String passwd){
		int check = -1;
		String dbPass = null;
		try {
			conn = getConnection();
			
			pstmt = conn.prepareStatement("select passwd, ref from board where num = ?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				dbPass = rs.getString("passwd");
				int ref = rs.getInt("ref");
				if(dbPass.equals(passwd)){
					pstmt = conn.prepareStatement("delete from board where ref=?");
					pstmt.setInt(1, ref);
					pstmt.executeUpdate();
					check = 1;
				}else{
					check = 0;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			freeConnection();
		}
		return check;
	}
}













